Add Custom Query Table

If you've selected the Direct Query Data Source option from the Data Flow, then you can add a custom table from the Tables panel in Data Modeling.

In this case, the SQL Editor can be used to write custom SQL queries on the data model. This option is supported when directly querying SQL databases (SQL Server, MySQL, PostgreSQL, Oracle, etc).

Once the script is created in the SQL Editor, the table produced by the script is added to the Tables panel.

Creating custom query tables allows you construct custom tables that don't exist in the data source. The custom table is then added to the data model; rather than being stored in the database schema (like query tables built in the data model) , it is executed at runtime.

Create a Custom Query Table

Click the 'Add a query' button from the ribbon, which will launch the SQL editor:

From the SQL editor, add a table name (orange highlight below). Expand the schemas to expose the data model (blue highlight below); double click on any tables, columns, or views to add them to the script window (yellow highlight).

Using the relevant buttons (green highlight), test your script, add the table to the data model, or cancel.

Once the script is added, the resulting table is added to the Tables panel. Custom query tables are denoted by a query icon.

Add a relationship between the new table and which table(s) it should be joined to.

• Click here to learn about joins.

To edit or delete a custom query table, right click on it and select 'Edit Query' or 'Delete' from the context menu: